﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using E4RPTools;
using HouseRental.DataSets;

namespace HouseRental
{
    public class Primavera
    {
        #region Private Methods

        private static long GetLastEstimate(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, int year)
        {
            const string query =
                @"  select  max(CDU_Numero)
                    from    TDU_Orcamento
                    where   CDU_Ano = @Ano";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Ano", year));
        }

        private static long GetEstimateId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_Id)
                    from    TDU_Orcamento";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertEstimate(SqlTransaction sqlTransaction, OrcamentoDataSet.OrcamentoRow estimate)
        {
            using (var sqlDataAccess = new SqlDataAccess())
                estimate.Numero = GetLastEstimate(sqlDataAccess, sqlTransaction, estimate.Ano) + 1;
            estimate.Nome = estimate.Ano + " - " + estimate.Numero.ToString().PadLeft(6, '0');
            const string query =
                @"  insert 
                    into    TDU_Orcamento(CDU_IdCondominio, CDU_Ano, CDU_Numero, CDU_Nome, CDU_Valor, CDU_Estado) 
                    values  (@IdCondominio, @Ano, @Numero, @Nome, @Valor, @Estado)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdCondominio", estimate.IdCondominio),
                                        new SqlParameter("Ano", estimate.Ano),
                                        new SqlParameter("Numero", estimate.Numero),
                                        new SqlParameter("Nome", estimate.Nome),
                                        new SqlParameter("Valor", estimate.Valor),
                                        new SqlParameter("Estado", estimate.Estado)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
            {
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
                estimate.Id = GetEstimateId(sqlDataAccess, sqlTransaction);
            }
        }

        private static void UpdateEstimate(SqlTransaction sqlTransaction, OrcamentoDataSet.OrcamentoRow estimate)
        {
            const string query =
                @"  update  TDU_Orcamento
                    set     CDU_Valor = @Valor
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", estimate.Id),
                                        new SqlParameter("Valor", estimate.Valor)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static long GetEstimateItemId(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  max(CDU_IdItem)
                    from    TDU_ItemOrcamento";
            return sqlDataAccess.ExecuteScalar(sqlTransaction, -1, query);
        }

        private static void InsertEstimateItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, OrcamentoDataSet.ItemOrcamentoRow estimateItem)
        {
            const string query =
                @"  insert 
                    into    TDU_ItemOrcamento(CDU_Id, CDU_Referencia, CDU_Descricao, CDU_Total, CDU_Janeiro, CDU_Fevereiro, CDU_Marco, CDU_Abril, CDU_Maio, CDU_Junho, 
                            CDU_Julho, CDU_Agosto, CDU_Setembro, CDU_Outubro, CDU_Novembro, CDU_Dezembro) 
                    values  (@Id, @Referencia, @Descricao, @Total, @Janeiro, @Fevereiro, @Marco, @Abril, @Maio, @Junho, 
                            @Julho, @Agosto, @Setembro, @Outubro, @Novembro, @Dezembro)";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", estimateItem.OrcamentoRow.Id),
                                        new SqlParameter("Referencia", estimateItem.Referencia),
                                        new SqlParameter("Descricao", estimateItem.IsDescricaoNull()? "" : estimateItem.Descricao),
                                        new SqlParameter("Total", estimateItem.Total),
                                        new SqlParameter("Janeiro", estimateItem.Janeiro),
                                        new SqlParameter("Fevereiro", estimateItem.Fevereiro),
                                        new SqlParameter("Marco", estimateItem.Marco),
                                        new SqlParameter("Abril", estimateItem.Abril),
                                        new SqlParameter("Maio", estimateItem.Maio),
                                        new SqlParameter("Junho", estimateItem.Junho),
                                        new SqlParameter("Julho", estimateItem.Julho),
                                        new SqlParameter("Agosto", estimateItem.Agosto),
                                        new SqlParameter("Setembro", estimateItem.Setembro),
                                        new SqlParameter("Outubro", estimateItem.Outubro),
                                        new SqlParameter("Novembro", estimateItem.Novembro),
                                        new SqlParameter("Dezembro", estimateItem.Dezembro)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
            estimateItem.IdItem = GetEstimateItemId(sqlDataAccess, sqlTransaction);
        }

        private static void UpdateEstimateItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, OrcamentoDataSet.ItemOrcamentoRow estimateItem)
        {
            const string query =
                @"  update  TDU_ItemOrcamento
                    set     CDU_Referencia = @Referencia, 
                            CDU_Descricao = @Descricao, 
                            CDU_Total = @Total, 
                            CDU_Janeiro = @Janeiro, 
                            CDU_Fevereiro = @Fevereiro, 
                            CDU_Marco = @Marco, 
                            CDU_Abril = @Abril, 
                            CDU_Maio = @Maio, 
                            CDU_Junho = @Junho, 
                            CDU_Julho = @Julho, 
                            CDU_Agosto = @Agosto, 
                            CDU_Setembro = @Setembro, 
                            CDU_Outubro = @Outubro, 
                            CDU_Novembro = @Novembro, 
                            CDU_Dezembro = @Dezembro
                    where   CDU_IdItem = @IdItem";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("IdItem", estimateItem.IdItem),
                                        new SqlParameter("Referencia", estimateItem.Referencia),
                                        new SqlParameter("Descricao", estimateItem.IsDescricaoNull()? "" : estimateItem.Descricao),
                                        new SqlParameter("Total", estimateItem.Total),
                                        new SqlParameter("Janeiro", estimateItem.Janeiro),
                                        new SqlParameter("Fevereiro", estimateItem.Fevereiro),
                                        new SqlParameter("Marco", estimateItem.Marco),
                                        new SqlParameter("Abril", estimateItem.Abril),
                                        new SqlParameter("Maio", estimateItem.Maio),
                                        new SqlParameter("Junho", estimateItem.Junho),
                                        new SqlParameter("Julho", estimateItem.Julho),
                                        new SqlParameter("Agosto", estimateItem.Agosto),
                                        new SqlParameter("Setembro", estimateItem.Setembro),
                                        new SqlParameter("Outubro", estimateItem.Outubro),
                                        new SqlParameter("Novembro", estimateItem.Novembro),
                                        new SqlParameter("Dezembro", estimateItem.Dezembro)
                                    };
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        private static void DeleteEstimateItem(SqlDataAccess sqlDataAccess, SqlTransaction sqlTransaction, DataRow estimateItem)
        {
            const string query =
                @"  delete  TDU_ItemOrcamento
                    where   CDU_IdItem = @IdItem";
            var estimateItemId = Convert.ToInt64(estimateItem["IdItem", DataRowVersion.Original]);
            sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("IdItem", estimateItemId));
        }

        private static bool ExistsLayout(SqlTransaction sqlTransaction, string lista, string nome)
        {
            int count;
            const string query =
                @"  select	count(*)
		            from	TDU_ListaLayout
                    where   CDU_Lista = @Lista
                            and CDU_Nome = @Nome";
            using (var dataAccess = new SqlDataAccess())
                count = dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Lista", lista),
                    new SqlParameter("Nome", nome));
            return count > 0;
        }

        private static void InsertLayout(SqlTransaction sqlTransaction, string list, string name, string xmlLayout)
        {
            const string query =
                @"  insert 
                    into    TDU_ListaLayout(CDU_Lista, CDU_Nome, CDU_XmlLayout) 
                    values  (@List, @Name, @XmlLayout)";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("List", list),
                    new SqlParameter("Name", name), new SqlParameter("XmlLayout", xmlLayout));
        }

        private static void UpdateLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            const string query =
                @"  update  TDU_ListaLayout 
                    set     CDU_XmlLayout = @XmlLayout
                    where   CDU_Lista = @Lista
                            and CDU_Nome = @Nome";
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("Lista", lista),
                    new SqlParameter("Nome", nome), new SqlParameter("XmlLayout", xmlLayout));
        }

        private static void GetJanuaryEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-01-01') as Mes,
		                    i.CDU_Janeiro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetFebruaryEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-02-01') as Mes,
		                    i.CDU_Fevereiro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetMarchEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-03-01') as Mes,
		                    i.CDU_Marco as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetAprilEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-04-01') as Mes,
		                    i.CDU_Abril as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetMayEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-05-01') as Mes,
		                    i.CDU_Maio as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetJuneEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-06-01') as Mes,
		                    i.CDU_Junho as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetJulyEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-07-01') as Mes,
		                    i.CDU_Julho as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetAugustEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-08-01') as Mes,
		                    i.CDU_Agosto as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetSeptemberEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-09-01') as Mes,
		                    i.CDU_Setembro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetOctoberEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-10-01') as Mes,
		                    i.CDU_Outubro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetNovemberEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-11-01') as Mes,
		                    i.CDU_Novembro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetDecemberEstimateAnalysis(SqlTransaction sqlTransaction, long? year, AnaliseOrcamentalDataSet dataSet)
        {
            var query =
                @"  select	o.CDU_Id as Id, 
		                    i.CDU_IdItem as IdItem,
		                    o.CDU_IdCondominio as IdCondominio,
		                    o.CDU_Ano as Ano, 
		                    o.CDU_Numero as Numero,
		                    o.CDU_Nome as Nome,
		                    i.CDU_Referencia as Referencia,
		                    i.CDU_Descricao as Descricao,
		                    Convert(datetime, Convert(varchar(4), o.CDU_Ano) + '-12-01') as Mes,
		                    i.CDU_Dezembro as Orcamentado,
		                    0 as Real		
                    from	TDU_Orcamento o, TDU_ItemOrcamento i
                    where	i.CDU_Id = o.CDU_Id";
            if (year.HasValue)
                query += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
        }

        private static void GetPurchaseDocuments(SqlTransaction sqlTransaction, AnalysisFilter analysisFilter, DocumentoDataSet dataSet)
        {
            if(!analysisFilter.Purchases) return;
            var query =
                @"  select	distinct 
		                    dc.descricao as TipoDocumento,
		                    cc.numdoc as NumeroDocumento, 
		                    lc.datadoc as DataDocumento,
		                    -(lc.totaliliquido - lc.totalda + lc.totaliva) as Valor,
		                    lc.Artigo,
		                    lc.descricao as DescricaoArtigo,
                            cc.Serie as SerieDocumento,
                            cc.Nome as EntidadeNome, 
                            cc.Morada as EntidadeMorada
                    from	CabecCompras cc, LinhasCompras lc, Artigo a, DocumentosCompra dc
                    where	cc.id = lc.idcabeccompras
		                    and a.artigo = lc.artigo
		                    and a.Cdu_controlaorcamento = 1
		                    and dc.documento = cc.tipodoc";
            if (analysisFilter.Year.HasValue) query += " and year(lc.datadoc) = " + analysisFilter.Year.Value;            
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Documento, query);            
        }

        public static void GetSaleDocuments(SqlTransaction sqlTransaction, AnalysisFilter analysisFilter, DocumentoDataSet dataSet)
        {
            if (!analysisFilter.Sales) return;            
            var query =
                @"  select	distinct 
		                    dv.descricao as TipoDocumento,
		                    cv.numdoc as NumeroDocumento, 
		                    lv.data as DataDocumento,
		                    lv.totaliliquido - lv.totalda + lv.totaliva as Valor,
		                    lv.Artigo,
		                    lv.descricao as DescricaoArtigo,
                            cv.Serie as SerieDocumento,
                            cv.NomeFac as EntidadeNome, 
                            cv.MoradaFac as EntidadeMorada
                    from	CabecDoc cv, LinhasDoc lv, Artigo a, DocumentosVenda dv
                    where	cv.id = lv.idcabecdoc
		                    and a.artigo = lv.artigo
		                    and a.Cdu_controlaorcamento = 1
		                    and dv.documento = cv.tipodoc";
            if (analysisFilter.Year.HasValue) query += " and year(lv.data) = " + analysisFilter.Year.Value;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Documento, query);            
        }

        public static void GetPaymentDocuments(SqlTransaction sqlTransaction, AnalysisFilter analysisFilter, DocumentoDataSet dataSet)
        {
            if (!analysisFilter.Payments) return;
//            var query =
//                @"  select	distinct 
//		                    dv.descricao as TipoDocumento,
//		                    cv.numdoc as NumeroDocumento, 
//		                    lv.data as DataDocumento,
//		                    lv.totaliliquido - lv.totalda + lv.totaliva as Valor,
//		                    lv.Artigo,
//		                    lv.descricao
//                    from	CabecDoc cv, LinhasDoc lv, Artigo a, DocumentosVenda dv
//                    where	cv.id = lv.idcabecdoc
//		                    and a.artigo = lv.artigo
//		                    and a.Cdu_controlaorcamento = 1
//		                    and dv.documento = cv.tipodoc";
//            if (analysisFilter.Year.HasValue) query += " and year(lv.data) = " + analysisFilter.Year.Value;
//            using (var dataAccess = new SqlDataAccess())
//                dataAccess.Fill(sqlTransaction, dataSet.Documento, query);            
        }

        #endregion

        public static CondominioDataSet GetHouseRentals(SqlTransaction sqlTransaction)
        {
            var dataSet = new CondominioDataSet();
            const string query =
                @"  select	codigo as id, 
		                    idnome as nome, idescada
                    from	empresas
                    where	idescada = '1'
                    order by idnome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Condomínio, query);
            return dataSet;
        }

        public static ProdutoDataSet GetProducts(SqlTransaction sqlTransaction)
        {
            var dataSet = new ProdutoDataSet();
            const string query =
                @"  select	distinct 
		                    artigo as referencia, 
                            descricao,
                            familia
                    from	artigo
                    where   CDU_ControlaOrcamento = 1
                    order by artigo, descricao";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Produto, query);
            return dataSet;
        }

        public static ProdutoDataSet GetProducts(SqlTransaction sqlTransaction, string family)
        {
            var dataSet = new ProdutoDataSet();
            const string query =
                @"  select	distinct 
		                    artigo as referencia, 
                            descricao,
                            familia
                    from	artigo
                    where   CDU_ControlaOrcamento = 1
                            and familia = @Family
                    order by familia, artigo, descricao";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Produto, query, new SqlParameter("Family", family));
            return dataSet;
        }

        public static FamiliaDataSet GetFamilies(SqlTransaction sqlTransaction)
        {
            var dataSet = new FamiliaDataSet();
            const string query =
                @"  select	distinct 
		                    familia as nome
                    from	artigo
                    where   CDU_ControlaOrcamento = 1
                    order by familia";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Familia, query);
            return dataSet;
        }

        public static DocumentoDataSet GetDocuments(SqlTransaction sqlTransaction, AnalysisFilter analysisFilter)
        {
            var dataSet = new DocumentoDataSet();
            GetPurchaseDocuments(sqlTransaction, analysisFilter, dataSet);
            GetSaleDocuments(sqlTransaction, analysisFilter, dataSet);
            GetPaymentDocuments(sqlTransaction, analysisFilter, dataSet);
            return dataSet;
        }

        public static OrcamentoDataSet GetEstimates(SqlTransaction sqlTransaction, string houseRentalId)
        {
            var dataSet = new OrcamentoDataSet();
            const string query =
                @"  select	CDU_Id as Id,
                            CDU_IdCondominio as IdCondominio, 
                            CDU_Ano as Ano, 
                            CDU_Valor as Valor, 
                            CDU_Numero as Numero, 
                            CDU_Nome as Nome,
                            CDU_Estado as Estado
                    from    TDU_Orcamento
                    where   CDU_IdCondominio = @IdCondominio
                    order by CDU_ano, CDU_numero";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query, new SqlParameter("IdCondominio", houseRentalId));
            return dataSet;
        }

        public static OrcamentoDataSet GetEstimate(SqlTransaction sqlTransaction, long estimateId)
        {
            var dataSet = new OrcamentoDataSet();
            const string query =
                @"  select	CDU_Id as Id,
                            CDU_IdCondominio as IdCondominio, 
                            CDU_Ano as Ano, 
                            CDU_Valor as Valor, 
                            CDU_Numero as Numero, 
                            CDU_Nome as Nome,
                            CDU_Estado as Estado
		            from	TDU_Orcamento
                    where   CDU_Id = @Id";
            const string itemQuery =
                @"  select	CDU_Id as Id,
                            CDU_IdItem as IdItem,
                            CDU_Referencia as Referencia,
                            CDU_Descricao as Descricao,
                            CDU_Total as Total,
                            CDU_Janeiro as Janeiro,
                            CDU_Fevereiro as Fevereiro,
                            CDU_Marco as Marco,
                            CDU_Abril as Abril,
                            CDU_Maio as Maio,
                            CDU_Junho as Junho,
                            CDU_Julho as Julho,
                            CDU_Agosto as Agosto,
                            CDU_Setembro as Setembro,
                            CDU_Outubro as Outubro,
                            CDU_Novembro as Novembro,
                            CDU_Dezembro as Dezembro
		            from	TDU_ItemOrcamento
                    where   CDU_Id = @Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query, new SqlParameter("Id", estimateId));
                dataAccess.Fill(sqlTransaction, dataSet.ItemOrcamento, itemQuery, new SqlParameter("Id", estimateId));
            }
            return dataSet;
        }

        public static bool ExistsEstimate(SqlTransaction sqlTransaction, long id, string houseRentalId, int year)
        {
            const string query =
                @"  select	1
		            from	TDU_Orcamento
                    where   CDU_IdCondominio = @IdCondominio
                            and CDU_Ano = @Ano
                            and CDU_Id <> @Id";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Id", id), 
                    new SqlParameter("IdCondominio", houseRentalId), new SqlParameter("Ano", year)) > 0;            
        }

        public static void SaveEstimate(SqlTransaction sqlTransaction, OrcamentoDataSet.OrcamentoRow estimate)
        {
            if (estimate.RowState == DataRowState.Added)
                InsertEstimate(sqlTransaction, estimate);
            else
                UpdateEstimate(sqlTransaction, estimate);
        }

        public static void UpdateEstimateItems(SqlTransaction sqlTransaction, OrcamentoDataSet.ItemOrcamentoDataTable estimateItems)
        {
            using (var sqlDataAccess = new SqlDataAccess())
            {
                foreach (var estimateItem in estimateItems)
                {
                    switch (estimateItem.RowState)
                    {
                        case DataRowState.Added:
                            InsertEstimateItem(sqlDataAccess, sqlTransaction, estimateItem);
                            break;
                        case DataRowState.Modified:
                            UpdateEstimateItem(sqlDataAccess, sqlTransaction, estimateItem);
                            break;
                        case DataRowState.Deleted:
                            DeleteEstimateItem(sqlDataAccess, sqlTransaction, estimateItem);
                            break;
                    }
                }
            }
        }

        public static ListaLayoutDataSet GetListLayouts(SqlTransaction sqlTransaction, string lista)
        {
            var dataSet = new ListaLayoutDataSet();
            const string query =
                @"  select	CDU_Lista as Lista, 
                            CDU_Nome as Nome,
                            CDU_XmlLayout as XmlLayout
		            from	TDU_ListaLayout
                    where   CDU_Lista = @Lista
                    order by CDU_Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.ListaLayout, query, new SqlParameter("Lista", lista));
            return dataSet;
        }

        public static void SaveLayout(SqlTransaction sqlTransaction, string lista, string nome, string xmlLayout)
        {
            if (ExistsLayout(sqlTransaction, lista, nome))
                UpdateLayout(sqlTransaction, lista, nome, xmlLayout);
            else
                InsertLayout(sqlTransaction, lista, nome, xmlLayout);

        }

        public static OrcamentoDataSet GetEstimate(SqlTransaction sqlTransaction, string houseRentalId, long year)
        {
            var dataSet = new OrcamentoDataSet();
            const string query =
                @"  select	CDU_Id as Id,
                            CDU_IdCondominio as IdCondominio, 
                            CDU_Ano as Ano, 
                            CDU_Valor as Valor, 
                            CDU_Numero as Numero, 
                            CDU_Nome as Nome,
                            CDU_Estado as Estado
		            from	TDU_Orcamento
                    where   CDU_IdCondominio = @HouseRentalId
                            and CDU_Ano = @Year";
            const string itemQuery =
                @"  select	i.CDU_Id as Id,
                            CDU_IdItem as IdItem,
                            CDU_Referencia as Referencia,
                            CDU_Descricao as Descricao,
                            CDU_Total as Total,
                            CDU_Janeiro as Janeiro,
                            CDU_Fevereiro as Fevereiro,
                            CDU_Marco as Marco,
                            CDU_Abril as Abril,
                            CDU_Maio as Maio,
                            CDU_Junho as Junho,
                            CDU_Julho as Julho,
                            CDU_Agosto as Agosto,
                            CDU_Setembro as Setembro,
                            CDU_Outubro as Outubro,
                            CDU_Novembro as Novembro,
                            CDU_Dezembro as Dezembro
		            from	TDU_ItemOrcamento i, TDU_Orcamento h
                    where   CDU_IdCondominio = @HouseRentalId
                            and CDU_Ano = @Year
                            and i.CDU_Id = h.CDU_Id";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query, new SqlParameter("HouseRentalId", houseRentalId), new SqlParameter("Year", year));
                dataAccess.Fill(sqlTransaction, dataSet.ItemOrcamento, itemQuery, new SqlParameter("HouseRentalId", houseRentalId), new SqlParameter("Year", year));
            }
            return dataSet;
        }

        public static AnaliseOrcamentalDataSet GetEstimateAnalysis(SqlTransaction sqlTransaction, long? year)
        {
            var dataSet = new AnaliseOrcamentalDataSet();
            GetJanuaryEstimateAnalysis(sqlTransaction, year, dataSet);
            GetFebruaryEstimateAnalysis(sqlTransaction, year, dataSet);
            GetMarchEstimateAnalysis(sqlTransaction, year, dataSet);
            GetAprilEstimateAnalysis(sqlTransaction, year, dataSet);
            GetMayEstimateAnalysis(sqlTransaction, year, dataSet);
            GetJuneEstimateAnalysis(sqlTransaction, year, dataSet);
            GetJulyEstimateAnalysis(sqlTransaction, year, dataSet);
            GetAugustEstimateAnalysis(sqlTransaction, year, dataSet);
            GetSeptemberEstimateAnalysis(sqlTransaction, year, dataSet);
            GetOctoberEstimateAnalysis(sqlTransaction, year, dataSet);
            GetNovemberEstimateAnalysis(sqlTransaction, year, dataSet);
            GetDecemberEstimateAnalysis(sqlTransaction, year, dataSet);
            return dataSet;
        }

        public static void UpdateEstimateState(SqlTransaction sqlTransaction, long estimateId, string state)
        {
            const string query =
                @"  update  TDU_Orcamento
                    set     CDU_Estado = @Estado
                    where   CDU_Id = @Id";
            var sqlParameters = new List<SqlParameter>
                                    {
                                        new SqlParameter("Id", estimateId),
                                        new SqlParameter("Estado", state)
                                    };
            using (var sqlDataAccess = new SqlDataAccess())
                sqlDataAccess.ExecuteNonQuery(sqlTransaction, query, sqlParameters.ToArray());
        }

        public static Collection<string> GetYears(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select  convert(varchar(4), CDU_Ano)
                    from    TDU_Orcamento";
            using (var sqlDataAccess = new SqlDataAccess())
                return sqlDataAccess.ExecuteStringList(sqlTransaction, query);
        }

        public static RelatorioDataSet GetReports(SqlTransaction sqlTransaction, string tipo)
        {
            var dataSet = new RelatorioDataSet();
            const string query =
                @"  select	CDU_Nome as Nome,
                            CDU_Ficheiro as Ficheiro,
                            CDU_Tipo as Tipo,
                            CDU_NomeXSD as NomeXSD
		            from	TDU_Relatorio
                    where   CDU_Tipo = @Tipo
                    order by CDU_Nome";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Relatorio, query, new SqlParameter("Tipo", tipo));
            return dataSet;
        }

        public static ImpressaoOrcamentoDataSet GetEstimates(SqlTransaction sqlTransaction, long? year)
        {
            var dataSet = new ImpressaoOrcamentoDataSet();
            var query =
                @"  select	CDU_IdCondominio as IdCondominio, 
                            CDU_Ano as Ano, 
                            CDU_Nome as Nome,
                            CDU_Estado as Estado,
		                    CDU_IdCondominio + CDU_Nome as IdCondominioNome,
		                    e.IDNome as Condominio,
		                    e.IDNumPorta as NumeroPorta,
		                    e.IDPorta as Porta,
		                    e.IDAndarSala as Andar				
                    from	TDU_Orcamento, PRIEMPRE.dbo.Empresas e
                    where	e.Codigo = CDU_IdCondominio";
            if (year.HasValue) query += " where CDU_Ano = " + year.Value; 
            var itemQuery =
                @"  select	o.CDU_IdCondominio + o.CDU_Nome as IdCondominioNome,
                            'Orçamento' as TipoItem,
                            CDU_Referencia as Referencia,
                            CDU_Descricao as Descricao,
                            CDU_Total as TotalEstimado,
                            0 as TotalReal,
                            CDU_Janeiro as JaneiroEstimado,
                            0 as JaneiroReal,
                            CDU_Fevereiro as FevereiroEstimado,
                            0 as FevereiroReal,
                            CDU_Marco as MarcoEstimado,
                            0 as MarcoReal,
                            CDU_Abril as AbrilEstimado,
                            0 as AbrilReal,
                            CDU_Maio as MaioEstimado,
                            0 as MaioReal,
                            CDU_Junho as JunhoEstimado,
                            0 as JunhoReal,
                            CDU_Julho as JulhoEstimado,
                            0 as JulhoReal,
                            CDU_Agosto as AgostoEstimado,
                            0 as AgostoReal,
                            CDU_Setembro as SetembroEstimado,
                            0 as SetembroReal,
                            CDU_Outubro as OutubroEstimado,
                            0 as OutubroReal,
                            CDU_Novembro as NovembroEstimado,
                            0 as NovembroReal,
                            CDU_Dezembro as DezembroEstimado,
		                    0 as DezembroReal
                    from	TDU_Orcamento o, TDU_ItemOrcamento io
                    where   io.CDU_Id = o.CDU_Id";
            if (year.HasValue) itemQuery += " and o.CDU_Ano = " + year.Value;
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Orcamento, query);
                dataAccess.Fill(sqlTransaction, dataSet.ItemOrcamento, itemQuery);
            }
            return dataSet;
        }
    }
}
